Common Relational Database Operations
In this lesson, we will look at some of the different operations that can be performed on relational databases.
We'll cover the following
We will concentrate on the three basic operations that can change the states of relations in the database: Insert, Delete, and Update. Insert is used to insert one or more new tuples in a relation, Delete is used to delete tuples, and Update is used to change the values of some attributes in existing tuples.
Whenever these operations are applied, the integrity constraints specified on the relational database schema should not be violated. So we will also discuss the types of constraints that may be violated by each of these operations along with the types of actions that may be taken if an operation causes a violation.
We will be using the database state illustrated below:
EMPLOYEE
| Name | Ssn | Bdate | Salary | Super_Ssn | Dept_Num |
|---|---|---|---|---|---|
| John Smith | 333445555 | 1968-05-22 | 45,000 | NULL | 3 |
| Emily Taylor | 987654321 | 1972-09-01 | 30,000 | 333445555 | 3 |
| Adam Kovac | 666884444 | 1969-04-09 | 55,000 | 333445555 | 3 |
| Kevin Jaimes | 888665555 | 1979-09-22 | 20,000 | 203948506 | 2 |
DEPARTMENT
| D_Name | D_No | Manager_Ssn |
|---|---|---|
| Administration | 3 | 333445555 |
| Research | 2 | 678884823 |
The insert operation#
The insert operation provides a list of attribute values for a new tuple t that is to be inserted into a relation R.
The insert operation can violate any of the four types of constraints:
- Domain constraints can be violated if an attribute value is given that does not appear in the corresponding domain or is not of the appropriate data type.
- Key constraints can be violated if the primary key value in the new tuple t already exists in another tuple in the relation.
- Entity integrity can be violated if any part of the primary key of the new tuple t is
NULL. - Referential integrity can be violated if the value of any foreign key in t refers to a tuple that does not exist in the referenced relation.
The following slides include examples of the insert operation:
If an insertion violates one or more constraints, the default option is to reject the insertion. In this case, it would be useful if the DBMS could provide a reason as to why the insertion was rejected.
The delete operation#
The delete operation can violate only referential integrity. This occurs if the tuple being deleted is referenced by foreign keys from other tuples in the database. To specify deletion, a condition of the attributes of the relation selects the tuple (or tuples) to be deleted.
Here are a few examples of the delete operation:
Several options are available if a deletion operation causes a violation. The first option, called restrict, is to reject the deletion. The second option, called cascade, is to attempt to cascade the deletion by deleting tuples that reference the tuple that is being deleted.
The update operation#
The update operation is used to change the values of one or more attributes in a tuple (or tuples) of some relation R. It is necessary to specify a condition on the attributes of the relation to select the tuple (or tuples) to be modified.
Here are a few examples of the update operation:
Updating an attribute that is neither part of a primary key nor part of a foreign key usually causes no problems; the DBMS need only check to confirm that the new value is of the correct data type and domain.
Modifying a primary key value is similar to deleting one tuple and inserting another in its place, because we use the primary key to identify tuples. Hence, the issues discussed earlier in insertion and deletion operations come into play.
The next lesson includes a quiz to test your knowledge regarding relational databases.
